﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using StandardDAC.Base;
using StandardDAC;
using MySql.Data.MySqlClient;

namespace MySqlDAC {
    /// <summary>
    /// Base DAC class for SQL Server that you provide it with a connection string when implemented. Commits
    /// any open transaction when disposed.
    /// </summary>
    public abstract class MySqlDac : IDisposable {
        #region members
        string connectionString;//connection string - set in abstract method: GetConnectionString()
        MySqlTransaction currentTran;//current transaction (usually null)
        MySqlDbController sql = new MySqlDbController();//sql-db-controller for all SQL Dac's in this project
        #endregion members

        #region properties
        /// <summary>
        /// Gets the current connection string. (set in GetConnectionString() method)
        /// </summary>
        public string ConnectionString {
            get {
                if (connectionString == null)
                    connectionString = GetConnectionString();
                return connectionString;
            }
        }
        /// <summary>
        /// returns any current transaction
        /// </summary>
        public MySqlTransaction Transaction {
            get { return currentTran; }
        }
        /// <summary>
        /// returns the base SqlDbController object
        /// </summary>
        protected MySqlDbController Sql {
            get { return sql; }
        }
        protected ConnInfo ConnInfo() {
            return new ConnInfo(ConnectionString);
        }
        protected TranInfo CurrentTranInfo() {
            if (currentTran == null) {
                currentTran = (MySqlTransaction)Sql.NewTransaction(connectionString);
            }
            return new TranInfo(currentTran);
        }
        #endregion properties

        #region Abstract Methods
        /// <summary>
        /// Abstract method by which a connection string is specified for the given DAC
        /// </summary>
        /// <returns></returns>
        protected abstract string GetConnectionString();
        #endregion Abstract Methods

        #region public methods
        /// <summary>
        /// Saves item to DB. (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that create procs are named "Table_Create" and update procs are labelled "Table_Update".
        /// </summary>
        /// <param name="item">item to save</param>
        /// <param name="table">prefix of the Table_Create or Table_Update stored procedure. Presumably the 
        /// table name</param>
        public void Save(IDataModel item, string table) {
            bool isNew = item.IsNew();
            object output = Sql.SaveObject(ConnInfo(), item,
                isNew ? table + "_Create" : table + "_Update", isNew);
            if (isNew && output != null)
                item.Id = (int)output;
        }
        /// <summary>
        /// Saves item to DB within current transaction. Uses Current Transaction. Starts new transaction 
        /// if no transaction exists.
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that create procs are named "Table_Create" and update procs are labelled "Table_Update".
        /// </summary>
        /// <param name="item">item to save</param>
        /// <param name="table">prefix of the Table_Create or Table_Update stored procedure. Presumably the 
        /// table name</param>
        public void SaveInTransaction(IDataModel item, string table) {
            bool isNew = item.IsNew();
            object output = Sql.SaveObject(CurrentTranInfo(), item,
                isNew ? table + "_Create" : table + "_Update", isNew);
            if (isNew && output != null)
                item.Id = (int)output;
        }
        /// <summary>
        /// Deletes item. (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that delete procs are named "Table_Delete"
        /// </summary>
        /// <param name="item">item to delete. Primary key must be set</param>
        /// <param name="table">prefix of the Table_Delete stored procedure. Presumably the table name</param>
        public void Delete(object item, string table) {
            Sql.DeleteObject(ConnInfo(), item, table + "_Delete");
        }
        /// <summary>
        /// Populates item. (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that the Primary keys are input parameters.
        /// </summary>
        /// <typeparam name="T">class type to populate</typeparam>
        /// <param name="item">item to populate</param>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name</param>
        public void ReadById<T>(T item, string table) {
            Sql.GetObjectById<T>(ConnInfo(), table + "_Read", item);
        }
        /// <summary>
        /// Returns list of all items in the database. (columns must be set up as DBColumn-Type attributes. 
        /// SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name</param>
        /// <returns></returns>
        public List<T> ReadAll<T>(string table) {
            return Sql.GetCollection<T>(ConnInfo(), table + "_Read");
        }
        /// <summary>
        /// Returns list of all items in the database for the given parameter. 
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="item">item to populate</param>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name.</param>
        /// <param name="parameter">parameter to pass to the stored procedure</param>
        /// <returns>flag indicating if object could be populated. (ie: a DataRow was returned)</returns>
        public bool ReadItemByParams<T>(T item, string table, MySqlParameter parameter) {
            return Sql.GetObjectByParams<T>(ConnInfo(), table + "_Read", item, new MySqlParameter[] { parameter });
        }
        /// <summary>
        /// Populates item from the database with the given parameters. (Does nothing if item isn't in database)
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="item">item to populate</param>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name.</param>
        /// <param name="parameters">parameters to pass to the stored procedure</param>
        /// <returns>flag indicating if object could be populated. (ie: a DataRow was returned)</returns>
        public bool ReadItemByParams<T>(T item, string table, MySqlParameter[] parameters) {
            return Sql.GetObjectByParams<T>(ConnInfo(), table + "_Read", item, parameters );
        }
        /// <summary>
        /// Returns list of all items in the database for the given parameter. 
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name.</param>
        /// <param name="parameter">parameter to pass to the stored procedure</param>
        /// <returns></returns>
        public List<T> ReadByParams<T>(string table, MySqlParameter parameter) {
            return Sql.GetCollection<T>(ConnInfo(), table + "_Read", new MySqlParameter[] { parameter });
        }
        /// <summary>
        /// Returns list of all items in the database for the given parameters. 
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name</param>
        /// <param name="parameters">parameters to pass to the stored procedure</param>
        /// <returns></returns>
        public List<T> ReadByParams<T>(string table, MySqlParameter[] parameters) {
            return Sql.GetCollection<T>(ConnInfo(), table + "_Read", parameters);
        }
        /// <summary>
        /// Returns keyed-id-collection of all items in the database. 
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name</param>
        /// <returns></returns>
        public ModelKeyedCollection<T> ReadKeyedAll<T>(string table)
            where T : IDataModel {
            ModelKeyedCollection<T> collection = new ModelKeyedCollection<T>();
            Sql.AddToCollection<T>(ConnInfo(), table + "_Read", collection);
            return collection;
        }
        /// <summary>
        /// Returns keyed-id-collection of all items in the database for the given parameter. 
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name.</param>
        /// <param name="parameter">parameter to pass to the stored procedure</param>
        /// <returns></returns>
        public ModelKeyedCollection<T> ReadKeyedByParams<T>(string table, MySqlParameter parameter)
            where T : IDataModel {
            ModelKeyedCollection<T> collection = new ModelKeyedCollection<T>();
            Sql.AddToCollection<T>(ConnInfo(), table + "_Read", new MySqlParameter[] { parameter }, collection);
            return collection;
        }
        /// <summary>
        /// Returns keyed-id-collection of all items in the database for the given parameters. 
        /// (columns must be set up as DBColumn-Type attributes. SqlColumn, etc. is ok).
        /// Assumes that read procs are named "Table_Read" and that passing no variables in displays all 
        /// records.
        /// </summary>
        /// <typeparam name="T">type of item to create a list of</typeparam>
        /// <param name="table">prefix of the Table_Read stored procedure. Presumably the table name.</param>
        /// <param name="parameters">parameters to pass to the stored procedure</param>
        /// <returns></returns>
        public ModelKeyedCollection<T> ReadKeyedByParams<T>(string table, MySqlParameter[] parameters)
            where T : IDataModel {
            ModelKeyedCollection<T> collection = new ModelKeyedCollection<T>();
            Sql.AddToCollection<T>(ConnInfo(), table + "_Read", parameters, collection);
            return collection;
        }

        public void ReadByParams<T>(string table, MySqlParameter parameter, IList<T> list) {
            Sql.AddToCollection<T>(ConnInfo(), table + "_Read", new MySqlParameter[] { parameter }, list);
        }
        public void ReadByParams<T>(string table, MySqlParameter[] parameters, IList<T> list) {
            Sql.AddToCollection<T>(ConnInfo(), table + "_Read", parameters, list);
        }

        /// <summary>
        /// Returns DataSet for the given stored procedure with the given parameters. 
        /// (null/empty parameters allowed)
        /// </summary>
        /// <param name="procedure">stored procedure to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed)</param>
        /// <returns></returns>
        public DataSet GetDataSet(string procedure, MySqlParameter[] parameters) {
            return Sql.GetDataSet(ConnInfo(), procedure, parameters);
        }
        /// <summary>
        /// Returns first DataTable for the given stored procedure with the given parameters. 
        /// (null/empty parameters allowed)
        /// </summary>
        /// <param name="procedure">stored procedure to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed)</param>
        /// <returns></returns>
        public DataTable GetDataTable(string procedure, MySqlParameter[] parameters) {
            return Sql.GetTable(ConnInfo(), procedure, parameters);
        }
        /// <summary>
        /// Returns first DataRow of first DataTable for the given stored procedure with the given parameters. 
        /// (null/empty parameters allowed)
        /// </summary>
        /// <param name="procedure">stored procedure to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed)</param>
        /// <returns></returns>
        public DataRow GetDataRow(string procedure, MySqlParameter[] parameters) {
            return Sql.GetRow(ConnInfo(), procedure, parameters);
        }
        /// <summary>
        /// Executes given stored procedure with the given parameters. (null/empty parameters allowed)
        /// </summary>
        /// <param name="procedure">stored procedure to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed)</param>
        public void Execute(string procedure, MySqlParameter[] parameters) {
            Sql.Execute(ConnInfo(), procedure, parameters);
        }
        /// <summary>
        /// Executes given stored procedure with the given parameters. Uses Current Transaction. Starts
        /// new transaction if no transaction exists. (null/empty parameters allowed)
        /// </summary>
        /// <param name="procedure">stored procedure to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed)</param>
        public DataSet ExecuteInTransaction(string procedure, MySqlParameter[] parameters) {
            return Sql.GetDataSet(CurrentTranInfo(), procedure, parameters);
        }
        /// <summary>
        /// Executes inline-Sql... You know the risks/benefits. Please use parameterized queries...
        /// Uses Current Transaction. Starts new transaction if no transaction exists. 
        /// (null/empty parameters allowed)
        /// </summary>
        /// <param name="sqlString">command text to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed</param>
        /// <returns></returns>
        public DataSet InlineSql_Execute(string sqlString, MySqlParameter[] parameters) {
            return Sql.InlineSql_Execute(ConnInfo(), sqlString, parameters);
        }
        /// <summary>
        /// Executes inline-Sql... You know the risks/benefits. Please use parameterized queries...
        /// Uses Current Transaction. Starts new transaction if no transaction exists. 
        /// (null/empty parameters allowed)
        /// </summary>
        /// <param name="sqlString">command text to execute</param>
        /// <param name="parameters">parameters to pass in. null/empty parameters allowed</param>
        /// <returns></returns>
        public DataSet InlineSql_ExecuteInTransaction(string sqlString, MySqlParameter[] parameters) {
            return Sql.InlineSql_Execute(CurrentTranInfo(), sqlString, parameters);
        }
        /// <summary>
        /// Commits current transaction if a transaction exists. Returns the transaction, in case you need it 
        /// for some reason... also resets this DAC's current transaction.
        /// </summary>
        /// <returns></returns>
        public MySqlTransaction TransactionCommit() {
            MySqlTransaction retValue = currentTran;
            if (currentTran != null) {
                currentTran.Commit();
                currentTran = null;
            }
            return retValue;
        }
        /// <summary>
        /// Rolls back the current transaction if a transaction exists. Returns the transaction, in case 
        /// you need it for some reason... also resets this DAC's current transaction.
        /// </summary>
        /// <returns></returns>
        public MySqlTransaction TransactionRollback() {
            MySqlTransaction retValue = currentTran;
            if (currentTran != null) {
                currentTran.Rollback();
                currentTran = null;
            }
            return retValue;
        }
        #endregion public methods

        #region public methods - helpers [STATIC]
        public static MySqlParameter newInParam(string name, object value) {
            MySqlParameter p = new MySqlParameter(name, value);
            return p;
        }
        public static MySqlParameter newInParam(string name, MySqlDbType type, object value) {
            MySqlParameter p = new MySqlParameter(name, value);
            p.MySqlDbType = type;
            return p;
        }
        public static MySqlParameter newOutParam(string name, MySqlDbType type) {
            MySqlParameter p = new MySqlParameter(name, type);
            p.Direction = ParameterDirection.Output;
            return p;
        }
        #endregion

        #region IDisposable Members
        /// <summary>
        /// Commits any current transactions that were opened. If you don't use transactions explicitly, then
        /// they aren't created.
        /// </summary>
        public void Dispose() {
            if (currentTran != null && currentTran.Connection != null) {
                currentTran.Commit();
                currentTran.Connection.Dispose();
            }
        }

        #endregion

        
    }
}